//===============================================================================
// Microsoft Architecture Strategy Team
// LitwareHR - SaaS Sample Application
//===============================================================================
// Copyright  Microsoft Corporation.  All rights reserved.
// THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY
// OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT
// LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND
// FITNESS FOR A PARTICULAR PURPOSE.
//===============================================================================
// The example companies, organizations, products, domain names,
// e-mail addresses, logos, people, places, and events depicted
// herein are fictitious.  No association with any real company,
// organization, product, domain name, email address, logo, person,
// places, or events is intended or should be inferred.
//===============================================================================

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Globalization;
using System.Data.Common;
using System.Data;
using Shp.Runtime.Services.Properties;
using Shp.Runtime.Contract;

namespace Shp.Runtime.Services
{
    static public class DatamodelLogic
    {
        static public StringPair[][] GetInstanceList(string tenantAlias, string entityDefName, Guid[] listToRetrieve)
        {
            StringBuilder sb = new StringBuilder();

            new List<Guid>(listToRetrieve).ForEach(new Action<Guid>(delegate(Guid id)
            {
                sb.AppendFormat("'{0}', ", id);
            }));
            string tmp = sb.ToString(0, sb.Length - 2);
            List<StringPair[]> list = new List<StringPair[]>();
            Database db = DatabaseFactory.CreateDatabase(Constants.DB.TenantDataStore);
            string sql = string.Format(CultureInfo.CurrentCulture, Constants.DB.SqlForSelectEntityList, entityDefName, tenantAlias, tmp);
            using (DbCommand command = db.GetSqlStringCommand(sql))
            {
                DataSet ds = db.ExecuteDataSet(command);
                List<StringPair> record;
                foreach (DataRow r in ds.Tables[0].Rows)
                {
                    record = new List<StringPair>();
                    int j = ds.Tables[0].Columns.Count;
                    for (int i = 0; i < j; i++)
                    {
                        StringPair pair = new StringPair();
                        pair.Key = ds.Tables[0].Columns[i].ColumnName;
                        pair.Value = r.ItemArray[i].ToString();
                        record.Add(pair);
                    }
                    list.Add(record.ToArray());
                }
            }
            return list.ToArray();
        }

        static public StringPair[] GetViewDefFields(string tenantAlias, string viewDefName)
        {
            Database db = DatabaseFactory.CreateDatabase(Constants.DB.TenantMetadataStore);
            List<StringPair> list = new List<StringPair>();
            using (DbCommand command = db.GetStoredProcCommand("dbo.GetViewDefFields"))
            {
                db.AddInParameter(command, "tenantAlias", DbType.String, tenantAlias);
                db.AddInParameter(command, "viewDefName", DbType.String, viewDefName);
                using (IDataReader reader = db.ExecuteReader(command))
                {
                    while (reader.Read())
                    {
                        StringPair pair = new StringPair();
                        pair.Key = reader.GetString(0);
                        pair.Value = reader.GetString(1);
                        if (StringPair.ContainsKey(pair.Key, list))
                        {
                            // An incosistent view definition has been added directly to the db
                            // you should guard against this behaviour
                            throw new System.Security.SecurityException(Resources.InvalidConfigurationDuplicateKey);
                        }
                        else
                        {
                            list.Add(pair);
                        }
                    }
                }
            }
            return list.ToArray();
        }

        static public StringPair[] GetViewDefFields(string tenantAlias, Guid viewDefId)
        {
            Database db = DatabaseFactory.CreateDatabase(Constants.DB.TenantMetadataStore);
            List<StringPair> list = new List<StringPair>();
            using (DbCommand command = db.GetStoredProcCommand("dbo.GetViewDefFieldsById"))
            {
                db.AddInParameter(command, "tenantAlias", DbType.String, tenantAlias);
                db.AddInParameter(command, "viewDefId", DbType.Guid, viewDefId);
                using (IDataReader reader = db.ExecuteReader(command))
                {
                    while (reader.Read())
                    {
                        StringPair pair = new StringPair();
                        pair.Key = reader.GetString(0);
                        pair.Value = reader.GetString(1);
                        if (StringPair.ContainsKey(pair.Key, list))
                        {
                            // An incosistent view definition has been added directly to the db
                            // you should guard against this behaviour
                            throw new System.Security.SecurityException(Resources.InvalidConfigurationDuplicateKey);
                        }
                        else
                        {
                            list.Add(pair);
                        }
                    }
                }
            }
            return list.ToArray();
        }

        static public EntityField[] GetEntityDefFields(string tenantAlias, string entityDefName)
        {
            Database db = DatabaseFactory.CreateDatabase(Constants.DB.TenantMetadataStore);
            List<EntityField> list = new List<EntityField>();
            using (DbCommand command = db.GetStoredProcCommand("dbo.GetEntityDefFields"))
            {
                db.AddInParameter(command, "tenantAlias", DbType.String, tenantAlias);
                db.AddInParameter(command, "entityDefName", DbType.String, entityDefName);
                using (IDataReader reader = db.ExecuteReader(command))
                {
                    while (reader.Read())
                    {
                        EntityField field = new EntityField();
                        field.Id = reader.GetGuid(0);
                        field.Name = reader.GetString(1);
                        field.TypeId = reader.GetGuid(2);
                        field.TypeName = reader.GetString(3);
                        field.IsShared = reader.GetBoolean(4);
                        list.Add(field);
                    }
                }
            }
            return list.ToArray();
        }

        static public Dictionary<Guid, string> GetEntityDefList()
        {
            Database db = DatabaseFactory.CreateDatabase(Constants.DB.TenantMetadataStore);
            Dictionary<Guid, string> list = new Dictionary<Guid, string>();
            using (DbCommand command = db.GetStoredProcCommand("dbo.GetEntityDefList"))
            {
                using (IDataReader reader = db.ExecuteReader(command))
                {
                    while (reader.Read())
                    {
                        list.Add(reader.GetGuid(0), reader.GetString(1));
                    }
                }
            }

            return list;
        }

        static public Dictionary<Guid, string> GetViewDefList()
        {
            Database db = DatabaseFactory.CreateDatabase(Constants.DB.TenantMetadataStore);
            Dictionary<Guid, string> list = new Dictionary<Guid, string>();
            using (DbCommand command = db.GetStoredProcCommand("dbo.GetViewDefList"))
            {
                using (IDataReader reader = db.ExecuteReader(command))
                {
                    while (reader.Read())
                    {
                        list.Add(reader.GetGuid(0), reader.GetString(1));
                    }
                }
            }

            return list;
        }

        static public GuidStringPair[] GetTypeList()
        {
            Database db = DatabaseFactory.CreateDatabase(Constants.DB.TenantMetadataStore);
            List<GuidStringPair> list = new List<GuidStringPair>();
            using (DbCommand command = db.GetStoredProcCommand("dbo.GetTypeList"))
            {
                using (IDataReader reader = db.ExecuteReader(command))
                {
                    while (reader.Read())
                    {
                        GuidStringPair record = new GuidStringPair();
                        record.Key = reader.GetGuid(0);
                        record.Value = reader.GetString(1);
                        list.Add(record);
                    }
                }
            }

            return list.ToArray();
        }

        static public void UpdateViewDef(Guid tenantId, Guid viewDefId, StringPair[] fields)
        {
            Database db = DatabaseFactory.CreateDatabase(Constants.DB.TenantMetadataStore);
            using (DbCommand command = db.GetStoredProcCommand("dbo.DeleteViewDef"))
            {
                db.AddInParameter(command, "tenantId", DbType.Guid, tenantId);
                db.AddInParameter(command, "viewDefId", DbType.Guid, viewDefId);
                db.ExecuteNonQuery(command);
            }
            int i = 0;
            foreach (StringPair pair in fields)
            {
                using (DbCommand command = db.GetStoredProcCommand("dbo.AddViewDefField"))
                {
                    db.AddInParameter(command, "tenantId", DbType.Guid, tenantId);
                    db.AddInParameter(command, "viewDefId", DbType.Guid, viewDefId);
                    db.AddInParameter(command, "name", DbType.String, pair.Value);
                    db.AddInParameter(command, "label", DbType.String, pair.Key);
                    db.AddInParameter(command, "ordinal", DbType.Int32, i);
                    db.ExecuteNonQuery(command);
                }
                i++;
            }
        }

        static public void UpdateEntityDef(Guid tenantId, Guid entityDefId, EntityField[] fields)
        {
            string tenantAlias;
            string entityDefName;
            Database db = DatabaseFactory.CreateDatabase(Constants.DB.TenantMetadataStore);
            using (DbCommand command = db.GetStoredProcCommand("dbo.GetTenantViewName"))
            {
                db.AddInParameter(command, "tenantId", DbType.Guid, tenantId);
                db.AddInParameter(command, "entityDefId", DbType.Guid, entityDefId);
                using (IDataReader reader = db.ExecuteReader(command))
                {
                    tenantAlias = "";
                    entityDefName = "";
                    while (reader.Read())
                    {
                        tenantAlias = reader.GetString(0);
                        entityDefName = reader.GetString(1);
                    }
                }
            }
            if (string.IsNullOrEmpty(tenantAlias))
                throw new ArgumentNullException("tenantAlias");
            if (string.IsNullOrEmpty(entityDefName))
                throw new ArgumentNullException("entityDefName");

            // make sure there are no duplicate fields
            List<string> list = new List<string>();
            foreach (EntityField field in fields)
            {
                if (!field.IsShared)
                {
                    if (list.Contains(field.Name))
                    {
                        throw new System.Security.SecurityException(Resources.InvalidConfigurationDuplicateKey);
                    }
                    else
                    {
                        list.Add(field.Name);
                    }                    
                }
            }

            using (DbCommand command = db.GetStoredProcCommand("dbo.DeleteExtensionField"))
            {
                db.AddInParameter(command, "tenantId", DbType.Guid, tenantId);
                db.AddInParameter(command, "entityDefId", DbType.Guid, entityDefId);
                db.ExecuteNonQuery(command);
            }

            foreach (EntityField item in fields)
            {
                if (!item.IsShared)
                {
                    using (DbCommand command = db.GetStoredProcCommand("dbo.AddExtensionField"))
                    {
                        db.AddInParameter(command, "tenantId", DbType.Guid, tenantId);
                        db.AddInParameter(command, "entityDefId", DbType.Guid, entityDefId);
                        db.AddInParameter(command, "typeId", DbType.Guid, item.TypeId);
                        db.AddInParameter(command, "name", DbType.String, item.Name);
                        db.ExecuteNonQuery(command);
                    }
                }
            }

            db = DatabaseFactory.CreateDatabase(Constants.DB.TenantDataStore);
            string sql = string.Format(CultureInfo.CurrentCulture, Constants.DB.DropView, entityDefName, tenantAlias);
            using (DbCommand command = db.GetSqlStringCommand(sql))
            {
                db.ExecuteNonQuery(command);
            }
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat(Constants.DB.CreateView, entityDefName, tenantAlias);
            foreach (EntityField item in fields)
            {
                if (item.IsShared)
                {
                    sb.AppendFormat("E.{0}, ", item.Name);
                }
            }
            sb.Remove(sb.Length - 2, 2);
            int i = 0;
            foreach (EntityField item in fields)
            {
                if (!item.IsShared)
                {
                    sb.AppendFormat(", X00{0}.value AS {1}", i, item.Name);
                    i++;
                }
            }
            sb.Append(" FROM PositionSharedValue AS E ");
            i = 0;
            foreach (EntityField item in fields)
            {
                if (!item.IsShared)
                {
                    sb.AppendFormat(" LEFT OUTER JOIN PositionCustomValue AS X00{0} ON E.id = X00{0}.instanceId AND X00{0}.extensionRef = '{1}' ", i, item.Name);
                    i++;
                }
            }
            sql = sb.ToString();
            using (DbCommand command = db.GetSqlStringCommand(sql))
            {
                db.ExecuteNonQuery(command);
            }
        }

        static public Guid AddInstance(Guid tenantId, string entityDefName, EntityField[] fields)
        {
            Database db = DatabaseFactory.CreateDatabase(Constants.DB.TenantDataStore);

            Guid instanceId = Guid.NewGuid();

            string sharedSqlCmd = DatamodelLogicHelper.GetSharedSchemaSqlCmd(Context.TenantAlias, entityDefName);
            // insert into shared schema
            using (DbCommand cmd = db.GetSqlStringCommand(sharedSqlCmd))
            {
                db.AddInParameter(cmd, "@id", DbType.Guid, instanceId);
                db.AddInParameter(cmd, "@tenantId", DbType.Guid, tenantId);

                foreach (EntityField field in fields)
                {
                    if (field.IsShared)
                    {
                        DbType type = DatamodelLogicHelper.GetDbType(Context.TenantAlias, entityDefName, field.Name);
                        db.AddInParameter(cmd, db.BuildParameterName(field.Name), type, field.Value);
                    }
                }

                db.ExecuteNonQuery(cmd);
            }

            string customSqlCmd = DatamodelLogicHelper.GetCustomShemaSqlCmd(Context.TenantAlias, entityDefName);
            // insert into custom schema
            foreach (EntityField field in fields)
            {
                if (!field.IsShared)
                {
                    using (DbCommand cmd = db.GetSqlStringCommand(customSqlCmd))
                    {
                        db.AddInParameter(cmd, "@id", DbType.Guid, Guid.NewGuid());
                        db.AddInParameter(cmd, "@instanceId", DbType.Guid, instanceId);
                        db.AddInParameter(cmd, "@extensionRef", DbType.String, field.Name);
                        db.AddInParameter(cmd, "@value", DbType.String, field.Value);

                        db.ExecuteNonQuery(cmd);
                    }
                }
            }

            return instanceId;
        }
    }
}